package com.wl.shixun4.dao;

import com.wl.shixun4.entity.Product;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ProductDAO {
    // 获取数据库连接
    private static Connection getConnection() {
        // 补充时区和SSL参数
        String url = "jdbc:mysql://localhost:3306/db01?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8";
        String user = "root";
        String password = "a15207291068";
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            return DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
            throw new RuntimeException("数据库连接失败", e);
        }
    }

    // 查询所有产品
    public static List<Product> findAll() {
        List<Product> list = new ArrayList<>();
        String sql = "SELECT * FROM product";
        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {
            while (rs.next()) {
                Product p = new Product();
                p.setId(rs.getLong("id"));
                p.setNumber(rs.getString("number"));
                p.setName(rs.getString("name"));
                p.setPrice(rs.getString("price"));
                p.setMadetime(rs.getTimestamp("madetime"));
                list.add(p);
            }
        } catch (SQLException e) {
            throw new RuntimeException("查询所有产品失败", e);
        }
        return list;
    }


    // 根据ID查找产品
    public static List<Product> findById(long id) {
        List<Product> list = new ArrayList<>();
        String sql = "SELECT * FROM product WHERE id = ?";
        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setLong(1, id);
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    Product p = new Product();
                    p.setId(rs.getLong("id"));
                    p.setNumber(rs.getString("number"));
                    p.setName(rs.getString("name"));
                    p.setPrice(rs.getString("price"));
                    p.setMadetime(rs.getTimestamp("madetime"));
                    list.add(p);
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("根据ID查找产品失败", e);
        }
        return list;
    }

    // 根据ID获取单个产品
    public static Product getById(long id) {
        String sql = "SELECT * FROM product WHERE id = ?";
        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setLong(1, id);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    Product p = new Product();
                    p.setId(rs.getLong("id"));
                    p.setNumber(rs.getString("number"));
                    p.setName(rs.getString("name"));
                    p.setPrice(rs.getString("price"));
                    p.setMadetime(rs.getTimestamp("madetime"));
                    return p;
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("根据ID查找产品失败", e);
        }
        return null;
    }

    // 根据性别查找产品
    public static List<Product> findByNumber(String number) {
        List<Product> list = new ArrayList<>();
        String sql = "SELECT * FROM product WHERE number = ?";
        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, number);
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    Product p = new Product();
                    p.setId(rs.getLong("id"));
                    p.setNumber(rs.getString("number"));
                    p.setName(rs.getString("name"));
                    p.setPrice(rs.getString("price"));
                    p.setMadetime(rs.getTimestamp("madetime"));
                    list.add(p);
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("根据性别查找产品失败", e);
        }
        return list;
    }

    // 根据产品名称查找产品
    public static List<Product> findByName(String name) {
        List<Product> list = new ArrayList<>();
        String sql = "SELECT * FROM product WHERE name LIKE ?";
        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, "%" + name + "%");
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    Product p = new Product();
                    p.setId(rs.getLong("id"));
                    p.setNumber(rs.getString("number"));
                    p.setName(rs.getString("name"));
                    p.setPrice(rs.getString("price"));
                    p.setMadetime(rs.getTimestamp("madetime"));
                    list.add(p);
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("根据产品名称查找产品失败", e);
        }
        return list;
    }


    // 根据身份信息查找人员（模糊匹配）
    public static List<Product> findRecorder(String minPrice, String maxPrice) {
        List<Product> list = new ArrayList<>();
        String sql = "SELECT * FROM product WHERE price LIKE ? OR price LIKE ?";
        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, "%" + minPrice + "%");
            stmt.setString(2, "%" + maxPrice + "%");
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    Product p = new Product();
                    p.setId(rs.getLong("id"));
                    p.setNumber(rs.getString("number"));
                    p.setName(rs.getString("name"));
                    p.setPrice(rs.getString("price"));
                    p.setMadetime(rs.getTimestamp("madetime"));
                    list.add(p);
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("根据价格范围查找产品失败", e);
        }
        return list;
    }


    // 新增产品
    public static boolean insert(Product product) {
        String sql = "INSERT INTO product (number, name, price, madetime) VALUES (?, ?, ?, ?)";
        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, product.getNumber());
            stmt.setString(2, product.getName());
            stmt.setString(3, product.getPrice());
            stmt.setTimestamp(4, product.getMadetime());
            int rowsAffected = stmt.executeUpdate();
            return rowsAffected > 0;
        } catch (SQLException e) {
            throw new RuntimeException("新增产品失败", e);
        }
    }

    // 从表单参数新增产品（处理参数验证和转换）
    public static boolean insertFromParams(String number, String name, String priceStr, String madetimeStr) {
        // 验证所有字段是否非空
        if (number == null || number.trim().isEmpty() || 
            name == null || name.trim().isEmpty() || 
            priceStr == null || priceStr.trim().isEmpty() || 
            madetimeStr == null || madetimeStr.trim().isEmpty()) {
            throw new IllegalArgumentException("所有字段都是必填的");
        }

        try {
            Product product = new Product();
            product.setNumber(number.trim());
            product.setName(name.trim());
            product.setPrice(priceStr.trim());
            
            // 处理datetime-local格式的日期时间字符串 (格式: yyyy-MM-ddTHH:mm)
            String datetimeStr = madetimeStr.replace("T", " ") + ":00";
            product.setMadetime(Timestamp.valueOf(datetimeStr));
            
            ProductDAO dao = new ProductDAO();
            return dao.insert(product);
        } catch (NumberFormatException e) {
            throw new IllegalArgumentException("价格格式不正确: " + priceStr, e);
        } catch (IllegalArgumentException e) {
            throw e;
        } catch (Exception e) {
            throw new RuntimeException("添加产品时出错: " + e.getMessage(), e);
        }
    }

    // 更新产品
    public static boolean update(Product product) {
        String sql = "UPDATE product SET number=?, name=?, price=?, madetime=? WHERE id=?";
        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, product.getNumber());
            stmt.setString(2, product.getName());
            stmt.setString(3, product.getPrice());
            stmt.setTimestamp(4, product.getMadetime());
            stmt.setLong(5, product.getId());
            int rowsAffected = stmt.executeUpdate();
            return rowsAffected > 0;
        } catch (SQLException e) {
            throw new RuntimeException("更新产品失败", e);
        }
    }

    // 部分更新产品（只更新提供的字段）
    public static boolean updatePartial(long id, String number, String name, Integer price, Timestamp madetime) {
        StringBuilder sql = new StringBuilder("UPDATE product SET ");
        java.util.List<Object> params = new java.util.ArrayList<>();
        boolean hasUpdate = false;

        if (number != null && !number.trim().isEmpty()) {
            sql.append("number=?");
            params.add(number);
            hasUpdate = true;
        }

        if (name != null && !name.trim().isEmpty()) {
            if (hasUpdate) sql.append(", ");
            sql.append("name=?");
            params.add(name);
            hasUpdate = true;
        }

        if (price != null) {
            if (hasUpdate) sql.append(", ");
            sql.append("price=?");
            params.add(price);
            hasUpdate = true;
        }

        if (madetime != null) {
            if (hasUpdate) sql.append(", ");
            sql.append("madetime=?");
            params.add(madetime);
            hasUpdate = true;
        }

        if (!hasUpdate) {
            // 没有要更新的字段
            return false;
        }

        sql.append(" WHERE id=?");
        params.add(id);

        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql.toString())) {
            for (int i = 0; i < params.size(); i++) {
                Object param = params.get(i);
                if (param instanceof String) {
                    stmt.setString(i + 1, (String) param);
                } else if (param instanceof Integer) {
                    stmt.setInt(i + 1, (Integer) param);
                } else if (param instanceof Timestamp) {
                    stmt.setTimestamp(i + 1, (Timestamp) param);
                } else if (param instanceof Long) {
                    stmt.setLong(i + 1, (Long) param);
                }
            }
            int rowsAffected = stmt.executeUpdate();
            return rowsAffected > 0;
        } catch (SQLException e) {
            throw new RuntimeException("更新产品失败", e);
        }
    }

    // 删除产品
    public static boolean delete(long id) {
        String sql = "DELETE FROM product WHERE id = ?";
        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setLong(1, id);
            int rowsAffected = stmt.executeUpdate();
            return rowsAffected > 0;
        } catch (SQLException e) {
            throw new RuntimeException("删除产品失败", e);
        }
    }

    // ========== 实训任务四：高级查询功能 ==========

    /**
     * 高级查询结果类
     */
    public static class QueryResult {
        private List<Product> products;
        private int totalCount;
        private int currentPage;
        private int pageSize;
        private int totalPages;

        public QueryResult(List<Product> products, int totalCount, int currentPage, int pageSize) {
            this.products = products;
            this.totalCount = totalCount;
            this.currentPage = currentPage;
            this.pageSize = pageSize;
            this.totalPages = (int) Math.ceil((double) totalCount / pageSize);
        }

        // Getters
        public List<Product> getProducts() { return products; }
        public int getTotalCount() { return totalCount; }
        public int getCurrentPage() { return currentPage; }
        public int getPageSize() { return pageSize; }
        public int getTotalPages() { return totalPages; }
    }

    /**
     * 排序信息类
     */
    public static class SortInfo {
        private String column;
        private String direction; // ASC 或 DESC
        private int priority; // 排序优先级，数字越小优先级越高

        public SortInfo(String column, String direction, int priority) {
            this.column = column;
            this.direction = direction;
            this.priority = priority;
        }

        // Getters
        public String getColumn() { return column; }
        public String getDirection() { return direction; }
        public int getPriority() { return priority; }
    }

    /**
     * 过滤信息类
     */
    public static class FilterInfo {
        private String column;
        private String operator; // =, LIKE, >, <, >=, <=, BETWEEN
        private String value;
        private String value2; // 用于BETWEEN操作

        public FilterInfo(String column, String operator, String value) {
            this.column = column;
            this.operator = operator;
            this.value = value;
        }

        public FilterInfo(String column, String operator, String value, String value2) {
            this.column = column;
            this.operator = operator;
            this.value = value;
            this.value2 = value2;
        }

        // Getters
        public String getColumn() { return column; }
        public String getOperator() { return operator; }
        public String getValue() { return value; }
        public String getValue2() { return value2; }
    }

    /**
     * 高级查询方法：支持多字段过滤、多字段排序、分页
     * @param filters 过滤条件列表
     * @param sorts 排序条件列表
     * @param page 页码（从1开始）
     * @param pageSize 每页大小
     * @return 查询结果
     */
    public static QueryResult advancedQuery(List<FilterInfo> filters, List<SortInfo> sorts, int page, int pageSize) {
        StringBuilder whereClause = new StringBuilder();
        StringBuilder orderClause = new StringBuilder();
        List<Object> params = new ArrayList<>();

        // 构建WHERE子句
        if (filters != null && !filters.isEmpty()) {
            whereClause.append(" WHERE ");
            for (int i = 0; i < filters.size(); i++) {
                if (i > 0) {
                    whereClause.append(" AND ");
                }
                FilterInfo filter = filters.get(i);
                whereClause.append(buildFilterCondition(filter, params));
            }
        }

        // 构建ORDER BY子句
        if (sorts != null && !sorts.isEmpty()) {
            orderClause.append(" ORDER BY ");
            // 按优先级排序
            sorts.sort((a, b) -> Integer.compare(a.getPriority(), b.getPriority()));
            for (int i = 0; i < sorts.size(); i++) {
                if (i > 0) {
                    orderClause.append(", ");
                }
                SortInfo sort = sorts.get(i);
                orderClause.append(sort.getColumn()).append(" ").append(sort.getDirection());
            }
        }

        // 先查询总数
        int totalCount = getTotalCount(whereClause.toString(), new ArrayList<>(params));

        // 构建分页查询
        String sql = "SELECT * FROM product" + whereClause.toString() + orderClause.toString() +
                    " LIMIT ? OFFSET ?";
        params.add(pageSize);
        params.add((page - 1) * pageSize);

        List<Product> products = executeQuery(sql, params);

        return new QueryResult(products, totalCount, page, pageSize);
    }

    /**
     * 构建过滤条件SQL
     */
    private static String buildFilterCondition(FilterInfo filter, List<Object> params) {
        String column = filter.getColumn();
        String operator = filter.getOperator();
        String value = filter.getValue();
        String value2 = filter.getValue2();

        switch (operator.toUpperCase()) {
            case "=":
                params.add(value);
                return column + " = ?";
            case "LIKE":
                params.add("%" + value + "%");
                return column + " LIKE ?";
            case ">":
                params.add(convertValue(column, value));
                return column + " > ?";
            case "<":
                params.add(convertValue(column, value));
                return column + " < ?";
            case ">=":
                params.add(convertValue(column, value));
                return column + " >= ?";
            case "<=":
                params.add(convertValue(column, value));
                return column + " <= ?";
            case "BETWEEN":
                params.add(convertValue(column, value));
                params.add(convertValue(column, value2));
                return column + " BETWEEN ? AND ?";
            default:
                throw new IllegalArgumentException("不支持的操作符: " + operator);
        }
    }

    /**
     * 根据列名转换值的类型
     */
    private static Object convertValue(String column, String value) {
        if (value == null || value.trim().isEmpty()) {
            return null;
        }

        switch (column) {
            case "id":
                return Long.parseLong(value);
            case "price":
                return value; // 身份信息现在是字符串类型
            case "madetime":
                return Timestamp.valueOf(value);
            default:
                return value;
        }
    }

    /**
     * 获取总记录数
     */
    private static int getTotalCount(String whereClause, List<Object> params) {
        String sql = "SELECT COUNT(*) FROM product" + whereClause;
        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            for (int i = 0; i < params.size(); i++) {
                setParameter(stmt, i + 1, params.get(i));
            }

            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt(1);
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("查询总数失败", e);
        }
        return 0;
    }

    /**
     * 执行查询并返回产品列表
     */
    private static List<Product> executeQuery(String sql, List<Object> params) {
        List<Product> list = new ArrayList<>();
        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            for (int i = 0; i < params.size(); i++) {
                setParameter(stmt, i + 1, params.get(i));
            }

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    Product p = new Product();
                    p.setId(rs.getLong("id"));
                    p.setNumber(rs.getString("number"));
                    p.setName(rs.getString("name"));
                    p.setPrice(rs.getString("price"));
                    p.setMadetime(rs.getTimestamp("madetime"));
                    list.add(p);
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("执行查询失败", e);
        }
        return list;
    }

    /**
     * 设置PreparedStatement参数
     */
    private static void setParameter(PreparedStatement stmt, int index, Object value) throws SQLException {
        if (value == null) {
            stmt.setNull(index, Types.NULL);
        } else if (value instanceof String) {
            stmt.setString(index, (String) value);
        } else if (value instanceof Integer) {
            stmt.setInt(index, (Integer) value);
        } else if (value instanceof Long) {
            stmt.setLong(index, (Long) value);
        } else if (value instanceof Timestamp) {
            stmt.setTimestamp(index, (Timestamp) value);
        } else {
            stmt.setObject(index, value);
        }
    }

    /**
     * 简化的查询方法：支持单一过滤和排序
     */
    public static QueryResult simpleQuery(String filterColumn, String filterValue,
                                        String sortColumn, String sortDirection,
                                        int page, int pageSize) {
        List<FilterInfo> filters = new ArrayList<>();
        List<SortInfo> sorts = new ArrayList<>();

        if (filterColumn != null && !filterColumn.trim().isEmpty() &&
            filterValue != null && !filterValue.trim().isEmpty()) {
            filters.add(new FilterInfo(filterColumn, "LIKE", filterValue));
        }

        if (sortColumn != null && !sortColumn.trim().isEmpty()) {
            String direction = (sortDirection != null && sortDirection.equalsIgnoreCase("DESC")) ? "DESC" : "ASC";
            sorts.add(new SortInfo(sortColumn, direction, 1));
        }

        return advancedQuery(filters, sorts, page, pageSize);
    }

}